﻿Imports System.Data.OleDb
Imports System.Data
Imports System.Configuration
Imports System.Drawing
Imports System.Web.UI
Imports System.Web.UI.WebControls
Public Class myphotodetail
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        hiddenUserName.Value = Context.User.Identity.Name
        BindRatingControl()
        SqlDataSource1.DataBind()
    End Sub
    Protected Sub bindrepeater()
        Dim item As ListViewItem
        item = ListView2.Items(0)
        Dim ds1 As SqlDataSource = DirectCast(item.FindControl("SqlDataSource5"), SqlDataSource)
        Dim ds2 As SqlDataSource = DirectCast(item.FindControl("SqlDataSource2"), SqlDataSource)
        Dim ds3 As SqlDataSource = DirectCast(item.FindControl("SqlDataSource4"), SqlDataSource)
        ds1.DataBind()
        ds2.DataBind()
        ds3.DataBind()
    End Sub
    Protected Sub BindRatingControl()
        Dim myphotoID As Integer = 0
        If Not Request.QueryString("PhotoID") Is Nothing Then
            myphotoID = CInt(Request.Params("PhotoID"))

            Dim total As Integer = 0
            Dim dt As New DataTable()
            Dim con As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("fishconn").ConnectionString)
            con.Open()
            Dim cmd As New OleDbCommand("SELECT Ratings AS RTNG FROM tblPhotoRating WHERE (PhotoID = @PhotoID)", con)
            cmd.Parameters.AddWithValue("@PhotoID", myphotoID)
            Dim da As New OleDbDataAdapter(cmd)
            da.Fill(dt)
            If dt.Rows.Count > 0 Then
                For i As Integer = 0 To dt.Rows.Count - 1
                    total += Convert.ToInt32(dt.Rows(i)(0).ToString())
                Next
                Dim average As Integer = total \ (dt.Rows.Count)
                Rating1.CurrentRating = average
            End If
            If dt.Rows.Count = 1 Then
                lbltxt.Text = dt.Rows.Count & " user has rated this Picture."
            End If

            If dt.Rows.Count > 1 Then
                lbltxt.Text = dt.Rows.Count & " users have rated this Picture."
            End If
            If dt.Rows.Count = 0 Then
                lbltxt.Text = "No  user have rated this Picture."
            End If
        End If
    End Sub
    Public Function ConvertDataTabletoString() As String

        Dim myphotoID As Integer = 0
        If Not Request.QueryString("PhotoID") Is Nothing Then
            myphotoID = CInt(Request.Params("PhotoID"))
        End If
        Dim dt As New DataTable()
        Dim con As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("fishconn").ConnectionString)
        Dim cmd As New OleDbCommand("SELECT LatLoc as lat, LongLoc as lng FROM tblPhoto WHERE (((tblPhoto.PhotoID)=[@PhotoID]));", con)
        cmd.Parameters.AddWithValue("@PhotoID", myphotoID)
        con.Open()
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        Dim serializer As New System.Web.Script.Serialization.JavaScriptSerializer()
        Dim rows As New List(Of Dictionary(Of String, Object))()
        Dim row As Dictionary(Of String, Object)
        For Each dr As DataRow In dt.Rows
            row = New Dictionary(Of String, Object)()
            For Each col As DataColumn In dt.Columns
                row.Add(col.ColumnName, dr(col))
            Next
            rows.Add(row)
        Next
        Return serializer.Serialize(rows)

    End Function

    Protected Sub Button2_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
        Dim myphotoid As Integer = 0
        If Not Request.QueryString("PhotoID") Is Nothing Then
            myphotoID = CInt(Request.Params("PhotoID"))

            Dim tusername As String = Context.User.Identity.Name
            Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("fishconn").ConnectionString)
            Dim SqlString As String = "UPDATE tblPhoto SET tblPhoto.Caption = @capt, tblPhoto.LatLoc = @latLoc, tblPhoto.LongLoc = @longloc WHERE tblPhoto.PhotoID=@photoid;"

            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd.CommandType = CommandType.Text
            Dim item As ListViewItem
            item = ListView2.Items(0)
            Dim txtcapt As TextBox = DirectCast(item.FindControl("txtCaption"), TextBox)
            Dim txtlatloc As TextBox = DirectCast(item.FindControl("TxtLat"), TextBox)
            Dim txtlongloc As TextBox = DirectCast(item.FindControl("TxtLong"), TextBox)
            Dim hidphotoid As HiddenField = DirectCast(item.FindControl("hiddenphotoid"), HiddenField)
            Dim hidsmallUrl As HiddenField = DirectCast(item.FindControl("Hiddensmallurl"), HiddenField)
            Dim prUrl As HiddenField = DirectCast(item.FindControl("HiddenUrl"), HiddenField)
            Dim chksetpropic As CheckBox = DirectCast(item.FindControl("chkProfilePict"), CheckBox)
            cmd.Parameters.AddWithValue("@capt", txtcapt.Text.ToString)
            cmd.Parameters.AddWithValue("@latloc", CSng(txtlatloc.Text))
            cmd.Parameters.AddWithValue("@longloc", CSng(txtlongloc.Text))
            cmd.Parameters.AddWithValue("@photoid", CInt(hidphotoid.Value))

            oleDbConn.Open()
            If cmd.ExecuteNonQuery() = 1 Then
                If chksetpropic.Checked Then
                    'change profile image
                    Dim SqlStringupdate As String = "Update tblProfile set ProfilePicture = @photo, ProfilePicturesmall = @smallphoto where UserName = @username"
                    If oleDbConn.State = ConnectionState.Closed Then
                        oleDbConn.Open()
                    End If
                    Dim cmdins As OleDbCommand = New OleDbCommand(SqlStringupdate, oleDbConn)
                    cmdins.CommandType = CommandType.Text
                    cmdins.Parameters.AddWithValue("@photo", prUrl.Value)
                    cmdins.Parameters.AddWithValue("@smallphoto", hidsmallUrl.Value)
                    cmdins.Parameters.AddWithValue("@username", tusername)
                    cmdins.ExecuteNonQuery()
                End If
                oleDbConn.Close()
                Response.Redirect("~/Account/myphotodetail.aspx?PhotoID=" + CStr(myphotoid))
            End If
        End If
        bindrepeater()
    End Sub

    Protected Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click

        Dim tusername As String = Context.User.Identity.Name
        Dim item As ListViewItem
        item = ListView2.Items(0)
        Dim hidphotoid As HiddenField = DirectCast(item.FindControl("hiddenphotoid"), HiddenField)
        'Check if is profile picture
        Dim oleDbConn As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("fishconn").ConnectionString)
        Dim SqlStringsel As String = "SELECT tblPhoto.PhotoID FROM tblProfile INNER JOIN tblPhoto ON tblProfile.ProfilePicture = tblPhoto.Url WHERE (((tblProfile.UserName) like [@username]));"
        Dim cmdsel As OleDbCommand = New OleDbCommand(SqlStringsel, oleDbConn)
        cmdsel.CommandType = CommandType.Text
        cmdsel.Parameters.AddWithValue("@username", tusername)
        oleDbConn.Open()
        Dim rs As OleDbDataReader = cmdsel.ExecuteReader
        Dim pictID As Integer
        pictID = 0
        If rs.Read() Then
            pictID = rs(0)
        End If
        If pictID = CInt(hidphotoid.Value) Then
            lblDeleteMsg.Text = "Cannot Delete the Profile Picture."
        Else
            lblDeleteMsg.Text = ""
            Dim SqlString As String = "UPDATE tblPhoto SET tblPhoto.Actv = 'D' WHERE tblPhoto.PhotoID=@photoid;"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, oleDbConn)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@photoid", CInt(hidphotoid.Value))
            If oleDbConn.State = ConnectionState.Closed Then
                oleDbConn.Open()
            End If
            If cmd.ExecuteNonQuery() = 1 Then
                oleDbConn.Close()
                Response.Redirect("~/Account/myphotos.aspx")
            End If
        End If
        bindrepeater()
    End Sub


    Protected Sub btnShowPopup_Click(sender As Object, e As EventArgs) Handles btnShowPopup.Click
        Dim myphotoID As Integer = 0
        If Not Request.QueryString("PhotoID") Is Nothing Then
            myphotoID = CInt(Request.Params("PhotoID"))

            Dim dt As New DataTable()
            Dim con As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("fishconn").ConnectionString)
            Dim cmd As New OleDbCommand("SELECT tblPhotoTag.TagID FROM tblTags INNER JOIN tblPhotoTag ON tblTags.TagID = tblPhotoTag.TagID WHERE (((tblPhotoTag.PhotoID)=[@PhotoID]));", con)
            cmd.Parameters.AddWithValue("@PhotoID", myphotoID)
            con.Open()
            Dim da As New OleDbDataAdapter(cmd)
            da.Fill(dt)

            For Each dr As DataRow In dt.Rows
                Dim tagid As Integer

                tagid = CInt(dr(0).ToString)

                For Each item As ListItem In CheckBoxList1.Items
                    If item.Value = tagid Then
                        item.Selected = True
                        Exit For
                    End If
                Next
            Next
            con.Close()
            Me.ModalPopupExtender1.Show()
        End If
        bindrepeater()
    End Sub

    Protected Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        'delete all tagswith photoid 
        Dim myphotoID As Integer = 0
        If Not Request.QueryString("PhotoID") Is Nothing Then
            myphotoID = CInt(Request.Params("PhotoID"))
            Dim con As New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("fishconn").ConnectionString)
            Dim delcmd As New OleDbCommand("DELETE from tblPhotoTag WHERE tblPhotoTag.PhotoID=@PhotoID;", con)
            delcmd.Parameters.AddWithValue("@PhotoID", myphotoID)
            con.Open()
            delcmd.ExecuteNonQuery()
            con.Close()
            ' then insert those selected
            For Each item As ListItem In CheckBoxList1.Items
                If item.Selected Then
                    Dim tagid As Integer = item.Value
                    Dim inscmd As New OleDbCommand("Insert into tblPhotoTag (PhotoID,TagID) values(@PhotoID, @TagID);", con)
                    inscmd.Parameters.AddWithValue("@PhotoID", myphotoID)
                    inscmd.Parameters.AddWithValue("@PhotoID", tagid)
                    con.Open()
                    inscmd.ExecuteNonQuery()
                    con.Close()

                End If
            Next
        End If
        bindrepeater()
        Response.Redirect("~/Account/myphotodetail.aspx?PhotoID=" + CStr(myphotoID))
    End Sub
End Class